﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Tender.Tool.SqlServer
{
    public class DbContext
    {
        /// <summary>
        /// 
        /// </summary>
        private SqlConnection connection_;
        private string SqlConfigstr_;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string SqlConfigstr
        {
            get 
            {
                if (string.IsNullOrEmpty(SqlConfigstr_))
                {
                    //string file = System.Windows.Forms.Application.ExecutablePath;
                    //System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(file);
                    string connectionString = ConfigurationManager.AppSettings["SQL"];
                    return connectionString;
                }
                else
                {

                    return SqlConfigstr_;
                }                         
            }
            set 
            {
                if (connection_ != null && SqlConfigstr != connection_.ConnectionString)
                {
                    connection_= new SqlConnection(SqlConfigstr);
                }
                SqlConfigstr_ = value; 
            }
        }

        public SqlConnection connection
        {
            get {
                if (connection_ != null && SqlConfigstr != connection_.ConnectionString)
                {
                    connection_ = new SqlConnection(SqlConfigstr);
                   return connection_;
                }
                if(connection_ == null)
                {
                    connection_ = new SqlConnection(SqlConfigstr);
                    return connection_;
                }
                else
                {
                    return connection_;
                }
               
            
                 }
        }
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql)
        {
            try
            {
                using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                Sunny.UI.UIMessageBox.ShowError(ex.Message,true,true); 
            }
           return null;
        }
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql,out string excep)
        {
            excep = "";
            try
            {
                using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                excep = ex.Message;
            }
            return null;
        }
        /// <summary>
        /// 查询返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> Where<T>(string sql) where T : class,new ()
        {
            using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
            {
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                connection.Close();
                return ListData<T>(dt);
            }
       
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Update(string sql)
        {
                try
                {
                    SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.Connection = connection;
                    sqlCommand.CommandText = sql;
                if(connection.State==ConnectionState.Closed)connection.Open();
                    return sqlCommand.ExecuteNonQuery();

                }
                catch
                {

                 }
                 finally
                {
                connection.Close();
                 }

            return 0;
        }          
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable) where T : class,new ()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();

            //遍历所有DataTable的行
            foreach (DataRow dr in dataTable.Rows)
            {
                var t = new T();
                //通过反射获取T类型的所有成员
                foreach (PropertyInfo pi in ps)
                {
                    if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                    {
                      
                        object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType,null);
                        pi.SetValue(t, value, null);
                    }
                }
                //将T类型添加到集合list
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable,out string exception) where T : class, new()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();
            exception = null;
            //遍历所有DataTable的行
            try
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    var t = new T();
                    //通过反射获取T类型的所有成员
                    foreach (PropertyInfo pi in ps)
                    {
                        if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                        {               
                            object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType,null);
                            //给T类型字段赋值
                            pi.SetValue(t, value, null);
                        }
                    }
                    //将T类型添加到集合list
                    list.Add(t);
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
            }
            return list;
        }
    }
}
